import java.io.IOException;
import java.io.Writer;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.sql.*;   // All we need for JDBC
import java.text.*;
import java.io.*;

import com.generationjava.io.*;
import com.generationjava.io.xml.*;

public class GeoCoverageWS
{

	Connection       db;        // A connection to the database
	Statement        sql;       // Our statement to run queries with
	DatabaseMetaData dbmd;
	
	public GeoCoverageWS() 
	{
		// Do nothing
	}

	public void GetTilesUrl(String database,String username,String password,int datatype,XmlWriter xw)
	throws ClassNotFoundException, SQLException, WritingException
	{
	    String res;
	    Class.forName("org.postgresql.Driver"); //load the driver
	    db = DriverManager.getConnection("jdbc:postgresql://192.168.0.16/"+database,
	                                     username,
	                                     password); //connect to the db
	    dbmd = db.getMetaData(); //get MetaData to confirm connection
	    System.out.println("Connection to "+dbmd.getDatabaseProductName()+" "+
	                       dbmd.getDatabaseProductVersion()+" successful.\n");
	    db.setAutoCommit(true);
	    
	    String query = null;
	    if (datatype==1)
	    {
	    	query = "SELECT * FROM dbo.gettilesurl()";
	    }
	    else if (datatype==2)
	    {
	    	query = "SELECT * FROM dbo.getcopyrtilesurl()";	    	
	    }
	    if (query != null)
	    {
	    	PreparedStatement proc = db.prepareStatement(query);
	    	ResultSet results = proc.executeQuery();
	   
	    	while (results.next())
	    	{	    	
	    		xw.writeEntity("tiles");
	    		xw.writeAttribute("guid", results.getString(1));
	    		xw.writeAttribute("tilesurl", results.getString(2));
	    		xw.writeAttribute("zoomlevel", results.getString(3));
	    		xw.writeAttribute("maptype", results.getString(4));
	    		xw.writeAttribute("provider", results.getString(5));
	    		xw.writeAttribute("covdate", results.getString(6));
	    		xw.endEntity();
	    	}
	    
	    	results.close();
	    }
	    db.close();
	}
	
	public void NextTilesWS(HttpServletRequest request, HttpServletResponse response)
	throws ServletException, IOException, ClassNotFoundException, SQLException 
	{
       
		try
		{
			response.setContentType("text/xml");
			Writer writer = new java.io.StringWriter();
			writer.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
	        XmlWriter xw = new XmlWriter(writer);
	        GetTilesUrl("geocoverage1","postgres","cov;geo",1,xw);
			xw.close();
			
			response.getOutputStream().write(writer.toString().getBytes("utf-8"));
			
		}
		catch( WritingException e)
		{
			response.getOutputStream().write("Error : exception catched!".getBytes("utf-8"));
		}
	}
	
	public void NextCopyrTilesWS(HttpServletRequest request, HttpServletResponse response)
	throws ServletException, IOException, ClassNotFoundException, SQLException 
	{
       
		try
		{
			response.setContentType("text/xml");
			Writer writer = new java.io.StringWriter();
			writer.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
	        XmlWriter xw = new XmlWriter(writer);
	        GetTilesUrl("geocoverage1","postgres","cov;geo",2,xw);
			xw.close();
			
			response.getOutputStream().write(writer.toString().getBytes("utf-8"));
			
		}
		catch( WritingException e)
		{
			response.getOutputStream().write("Error : exception catched!".getBytes("utf-8"));
		}
	}

	public void GetCity(String database,String username,String password,String providerType,XmlWriter xw)
	throws ClassNotFoundException, SQLException, WritingException
	{
	    String res;
	    Class.forName("org.postgresql.Driver"); //load the driver
	    db = DriverManager.getConnection("jdbc:postgresql://192.168.0.16/"+database,
	                                     username,
	                                     password); //connect to the db
	    dbmd = db.getMetaData(); //get MetaData to confirm connection
	    db.setAutoCommit(true);
	    
	    PreparedStatement proc = db.prepareStatement("select * from dbo.getcity(?,?)");
	    
	    Date date = getCurrentJavaSqlDate();
	    proc.setString(1, providerType);
	    proc.setDate(2, date);
	    ResultSet results = proc.executeQuery();
	   
	    while (results.next())
	    {	    	
	    	xw.writeEntity("city");
			xw.writeAttribute("guid", results.getString(1));
			xw.writeAttribute("name", results.getString(2));
			xw.writeAttribute("X", results.getString(3));
			xw.writeAttribute("Y", results.getString(4));
			xw.endEntity();
	    }
	    
	    results.close();
	    db.close();
	}
	
	public void NextCityWS(HttpServletRequest request, HttpServletResponse response)
	throws ServletException, IOException, ClassNotFoundException, SQLException 
	{
       
		try
		{
			response.setContentType("text/xml");
			Writer writer = new java.io.StringWriter();
			writer.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
	        XmlWriter xw = new XmlWriter(writer);
			//xw.writeEntity("Test");
	        String Providertype = request.getParameter("Providertype");
			GetCity("geocoverage1","postgres","cov;geo",Providertype,xw);
			//xw.endEntity();
			xw.close();
			
			response.getOutputStream().write(writer.toString().getBytes("utf-8"));
			
		}
		catch( WritingException e)
		{
			response.getOutputStream().write("Error : exception catched!".getBytes("utf-8"));
		}
	}

	
	 public static java.sql.Date getCurrentJavaSqlDate() {
		    java.util.Date today = new java.util.Date();
		    return new java.sql.Date(today.getTime());
	 }
	public String PGWrtbCoverage(String database,String username,String password,int gid,
	int CovCode,String CovValue,int ZoomLevel, String MapType, int EndProcess)
	throws ClassNotFoundException, SQLException, WritingException
	{
		try
		{
			String resul = "OK";
		    Class.forName("org.postgresql.Driver"); //load the driver
		    db = DriverManager.getConnection("jdbc:postgresql://192.168.0.16/"+database,
		                                     username,
		                                     password); //connect to the db
		    dbmd = db.getMetaData(); //get MetaData to confirm connection
		    //System.out.println("Connection to "+dbmd.getDatabaseProductName()+" "+
		    //                   dbmd.getDatabaseProductVersion()+" successful.\n");

		    db.setAutoCommit(true);
		    
		    PreparedStatement proc = db.prepareStatement("SELECT * FROM dbo.WriteResults(?,?,?,?,?,?)");
		    proc.setLong(1, gid); 
		    proc.setInt(2, CovCode); 
		    proc.setString(3, CovValue); 
		    proc.setInt(4, ZoomLevel); 
		    Date date = getCurrentJavaSqlDate();
		    proc.setDate(5, date);
		    proc.setString(6, MapType);
		    
		    ResultSet results = proc.executeQuery();
		    results.next();
		    int resu = results.getInt(1);	   
		    results.close();
		    
		    if (EndProcess !=0)
		    {
		    	if (EndProcess==1)
		    	{
		    		proc = db.prepareStatement("SELECT * FROM dbo.updatecitytiles(?,?,?,?)");
		    		proc.setLong(1, gid);   
				    proc.setInt(2, ZoomLevel); 
				    Date date1 = getCurrentJavaSqlDate();
				    proc.setDate(3, date1);
				    proc.setString(4, MapType);	
				    
				    results = proc.executeQuery();
				    results.next();
				    resu = results.getInt(1);	   
				    results.close();
				    resul = "eee";
		    	}
		    	if (EndProcess==2)
		    	{
		    		proc = db.prepareStatement("SELECT * from dbo.updatecityprocess(?,?)");
		    		proc.setLong(1, gid); 
				    Date date2 = getCurrentJavaSqlDate();
				    proc.setDate(2, date2);	
				
				    results = proc.executeQuery();
				    results.next();
				    resu = results.getInt(1);	   
				    results.close();
				    resul = "eeer";
		    	}
		    	if (EndProcess==3)
		    	{
		    		proc = db.prepareStatement("SELECT * FROM dbo.updatecopyrtiles(?,?,?,?)");
		    		proc.setLong(1, gid);   
				    proc.setInt(2, ZoomLevel); 
				    Date date1 = getCurrentJavaSqlDate();
				    proc.setDate(3, date1);
				    proc.setString(4, MapType);	
				    
				    results = proc.executeQuery();
				    results.next();
				    resu = results.getInt(1);	   
				    results.close();
				    resul = "eee";
		    	}
		    }

		    db.close();
	   
		    return resul;
		}
		catch(SQLException e) {
            System.out.println(e);
            return e.getMessage();
        } 
	}
	
	public String PGTiles(String database,String username,String password,String gid,
			String ZoomLevel,String tilesUrl,String Maptype, int datatype)
			throws ClassNotFoundException, SQLException, WritingException
			{
				try
				{
				    Class.forName("org.postgresql.Driver"); //load the driver
				    db = DriverManager.getConnection("jdbc:postgresql://192.168.0.16/"+database,
				                                     username,
				                                     password); //connect to the db
				    dbmd = db.getMetaData(); //get MetaData to confirm connection
				    System.out.println("Connection to "+dbmd.getDatabaseProductName()+" "+
				                       dbmd.getDatabaseProductVersion()+" successful.\n");
				    //sql = db.createStatement(); //create a statement that we can use later
				    db.setAutoCommit(true);
				    
				    String query = null;
				    if (datatype==1)
				    {
				    	query = "SELECT * FROM dbo.writecitytiles(?,?,?,?,?)";
				    }
				    else if (datatype==2)
				    {
				    	query = "SELECT * FROM dbo.writecopyrtiles(?,?,?,?,?)";	    	
				    }
				    if (query != null)
				    {
				    	PreparedStatement proc = db.prepareStatement(query);
				    	proc.setLong(1, Integer.parseInt(gid)); 
				    	proc.setString(2, tilesUrl); 
				    	proc.setInt(3, Integer.parseInt(ZoomLevel));
				    	proc.setString(4, Maptype);
				    	Date date = getCurrentJavaSqlDate();
				    	proc.setDate(5, date);
				    	ResultSet results = proc.executeQuery();
				    
				    	results.next();			   
				    	int resu = results.getInt(1);	   
				    	results.close();
				    }
				    
				    db.close();
			   
				    return "";
				}
				catch(SQLException e) {
		            System.out.println(e);
		            return e.getMessage();
		        } 
			}

	public void WriteCopyrTiles(HttpServletRequest request, HttpServletResponse response)
	throws ServletException, IOException, ClassNotFoundException, SQLException 
	{
       
		try
		{
			//Get HTTP parameter
			String gid =request.getParameter("GUID");
			String TilesUrl = request.getParameter("tilesUrl");
			String ZoomLevel = request.getParameter("ZoomLevel");
			String MapType = request.getParameter("MapType");
			String resp = PGTiles("geocoverage1","postgres","cov;geo",gid,ZoomLevel,TilesUrl,MapType,2);
			
			response.getOutputStream().write("OK\n".getBytes("utf-8"));
			response.getOutputStream().write(resp.getBytes("utf-8"));
		}
		catch( WritingException e)
		{
			response.getOutputStream().write("Error : exception catched!\n".getBytes("utf-8"));
			response.getOutputStream().write(e.getMessage().getBytes("utf-8"));
		}
	}
	
	public void WriteCityTiles(HttpServletRequest request, HttpServletResponse response)
	throws ServletException, IOException, ClassNotFoundException, SQLException 
	{
       
		try
		{
			//Get HTTP parameter
			String gid =request.getParameter("GUID");
			String TilesUrl = request.getParameter("tilesUrl");
			String ZoomLevel = request.getParameter("ZoomLevel");
			String MapType = request.getParameter("MapType");
			String resp = PGTiles("geocoverage1","postgres","cov;geo",gid,ZoomLevel,TilesUrl,MapType,1);
			
			response.getOutputStream().write("OK\n".getBytes("utf-8"));
			response.getOutputStream().write(resp.getBytes("utf-8"));
		}
		catch( WritingException e)
		{
			response.getOutputStream().write("Error : exception catched!\n".getBytes("utf-8"));
			response.getOutputStream().write(e.getMessage().getBytes("utf-8"));
		}
	}
	public void WriteResDatabase(HttpServletRequest request, HttpServletResponse response)
	throws ServletException, IOException, ClassNotFoundException, SQLException 
	{
       
		try
		{
			//Get HTTP parameter
			int gid =Integer.parseInt(request.getParameter("GID"));
			int CovCode = Integer.parseInt(request.getParameter("CovCode"));
			String CovValue = request.getParameter("CovValue");
			int ZoomLevel = Integer.parseInt(request.getParameter("ZoomLevel"));
			String MapType = request.getParameter("MapType");
			int EndProcess = Integer.parseInt(request.getParameter("EndProcess"));
			String resp = PGWrtbCoverage("geocoverage1","postgres","cov;geo",gid,CovCode,CovValue,ZoomLevel,MapType,EndProcess);
			
			response.getOutputStream().write("OK\n".getBytes("utf-8"));
			
		}
		//catch( WritingException e)
		catch (Exception e)
		{
			response.getOutputStream().write("Error : exception catched!\n".getBytes("utf-8"));
		}
	}
	
	public void ESSAI(HttpServletRequest request, HttpServletResponse response)
	throws ServletException, IOException, ClassNotFoundException, SQLException 
	{
       
		try
		{
			response.getOutputStream().write("OK\n".getBytes("utf-8"));
				
		}
		catch( Exception e)
		{
			response.getOutputStream().write("Error : exception catched!\n".getBytes("utf-8"));
		}
	}
}